﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.SharePoint.Client;
using System.Windows.Forms;
using System.Data;
using System.Collections;
using System.Drawing;
using System.IO;

namespace SPMigrationOracleUtility
{
    class Map_Migration
    {
       
        private string DataSource = "127.0.0.1";
        private string UserId = "hr";
        private string Password = "pass@word1";
      
        //private string UserId = "jtc";
        //private string Password = "tiger";

        private string strConResult;

        DataTable dtResults;
        public string webURL { get; set; }

        public string SiteUrl { get; set; }
        
        Hashtable ohtListName ; 
        Hashtable ohtfldMapping ;
        MappingColumns mapColumns = new MappingColumns();

        public Map_Migration()
        {
            SiteUrl = Convert.ToString(SPMigrationOracleUtility.Appl.Default.SiteURL);
        }

        public void Getdatas(CheckedListBox chkLstBox)
        {
            try
            {
                chkLstBox.DisplayMember = "MappingName";
                using (var clientContext = new ClientContext(SiteUrl))
                {
                    List list = clientContext.Web.Lists.GetByTitle("Mapping List");
                    CamlQuery query = CamlQuery.CreateAllItemsQuery();
                    ListItemCollection listItems = list.GetItems(query);
                    clientContext.Load(listItems);
                    clientContext.ExecuteQuery();
                    foreach (var Item in listItems)
                    {
                        chkLstBox.Items.Add(new MappingListValues{ MappingName = Convert.ToString(Item["Mapping_Name"]), Description= Convert.ToString(Item["Description"]),  Itemid = Convert.ToInt32 (Item["ID"]) , AllItems= Convert.ToString (Item["All_Items"]), Query=Convert.ToString(Item["Query"]), viewName= Convert.ToString(Item["View_Name"]) });
                    }
                }
                        
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
             
        }

        public void importList(MappingListValues oMapList,ProgressBar progressBar,ListView lstView)
        {
            ohtListName = new Hashtable();
            ohtfldMapping = new Hashtable();
            try
            {
                using (ClientContext ctx = new ClientContext(SiteUrl))
                {
                    lstView.View = System.Windows.Forms.View.Details;
                    ListViewItem lvi1 = lstView.Items.Add(oMapList.MappingName);
                    lvi1.SubItems.Add("Collecting mapped details for '" + oMapList.MappingName + "'  Mapping");
                    List list = ctx.Web.Lists.GetByTitle("Mapping Column List");
                    CamlQuery query = new CamlQuery();
                    query.ViewXml = "<View><Query><Where><Eq><FieldRef Name='Mapping_ID'/><Value Type='Text'>" + Convert.ToString(oMapList.Itemid) + "</Value></Eq></Where></Query></View>";
                    ListItemCollection listItems = list.GetItems(query);
                    ctx.Load(listItems);
                    ctx.ExecuteQuery();
                    foreach (var lstItem in listItems)
                    {     
                        FieldUrlValue oweburlval = (FieldUrlValue)lstItem["Web_Url"];
                        webURL = oweburlval.Url;
                        mapColumns.srcTableName = Convert.ToString(lstItem["Source_Table_Name"]);
                        mapColumns.destListName = Convert.ToString(lstItem["Destination_List_Name"]);
                        mapColumns.ListType = Convert.ToString(lstItem["Destination_List_Template"]);

                        if (!ohtListName.ContainsKey(mapColumns.destListName))
                            ohtListName.Add(mapColumns.destListName, mapColumns.destListName);
                        ohtfldMapping.Add(mapColumns.destListName + "." + Convert.ToString(lstItem["Destination_Field"]), mapColumns.srcTableName + "." + Convert.ToString(lstItem["Source_Field"]));

                        lvi1 = lstView.Items.Add("");
                        lvi1.SubItems.Add("Source Table / View :  '" + Convert.ToString(mapColumns.srcTableName) + "'      Destination ListName / Library  :  '" + Convert.ToString(mapColumns.destListName) +"' ");

                        lvi1 = lstView.Items.Add("");
                        lvi1.SubItems.Add("Source Field :  " + Convert.ToString(lstItem["Source_Field"]) + "         Destination Field :  " + Convert.ToString(lstItem["Destination_Field"]));
                        
                    }
                    lvi1 = lstView.Items.Add("");
                    lvi1.SubItems.Add("Fields are mapped Successfully");
                    Web oweb ;
                    Uri SiteUri = new Uri(SiteUrl );
                    Uri webUri = new Uri(webURL );
                    if (SiteUri.AbsolutePath == webUri.AbsolutePath )
                    {
                        oweb = ctx.Web;
                    }
                    else
                    {
                        oweb = getWeb(webURL);
                    }
                    foreach (string destlist in ohtListName.Keys)
                    {
                        List olist = oweb.Lists.GetByTitle(destlist);
                        ctx.Load(olist);
                        ctx.ExecuteQuery();
                        progressBar.PerformStep();
                        lvi1 = lstView.Items.Add("");
                        lvi1.SubItems.Add("Connecting with Oracle");
                        strConResult = orcConnection.Connect(DataSource, UserId, Password);
                        lvi1 = lstView.Items.Add("");
                        lvi1.SubItems.Add("Connected Succesfully");
                        if (oMapList.AllItems == "True")
                        {
                            string qry = HtmlRemoval.StripTagsRegex(oMapList.Query);
                            if (string.IsNullOrEmpty(qry))
                            {
                                // Get Table Name
                                progressBar.PerformStep();
                                lvi1 = lstView.Items.Add("");
                                lvi1.SubItems.Add("Searching for Table '" + mapColumns.srcTableName + "'");
                                dtResults = orcConnection.GetObjectData(mapColumns.srcTableName);
                            }
                            else
                            {
                                if (qry.Length > 0)
                                {
                                    // Get Table Name
                                    progressBar.PerformStep();
                                    lvi1 = lstView.Items.Add("");
                                    lvi1.SubItems.Add(" Searching for Table '" + mapColumns.srcTableName + "'");
                                    dtResults = orcConnection.GetObjectData(mapColumns.srcTableName);
                                }
                                else
                                {
                                    // get view name
                                    progressBar.PerformStep();
                                    lvi1 = lstView.Items.Add("");
                                    lvi1.SubItems.Add("Searching for View '" + mapColumns.srcTableName + "'");
                                    dtResults = orcConnection.GetObjectData(oMapList.viewName);
                                }
                            }
                        }
                        else
                        {
                            //pass the query
                            progressBar.PerformStep();
                            lvi1 = lstView.Items.Add("");
                            lvi1.SubItems.Add("Retriving records from '" + mapColumns.srcTableName + "' based on Query '" + oMapList.Query + "'");
                            dtResults = orcConnection.ExecuteSql(oMapList.Query);
                        }
                        if (dtResults != null)
                        {
                            progressBar.PerformStep();
                            if (dtResults.Rows.Count > 0)
                            {
                                lvi1 = lstView.Items.Add("");
                                lvi1.SubItems.Add("Found " + dtResults.Rows.Count + " Records ");
                                progressBar.PerformStep();
                                foreach (DataRow dtrow in dtResults.Rows)
                                {
                                    ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
                                    ListItem oListItem = olist.AddItem(itemCreateInfo);
                                    foreach (string destfld in ohtfldMapping.Keys)
                                    {
                                        oListItem[destfld.Split('.')[1]] = dtrow[Convert.ToString(ohtfldMapping[destfld]).Split('.')[1]];
                                        lvi1 = lstView.Items.Add("");
                                        lvi1.SubItems.Add("'" + Convert.ToString(ohtfldMapping[destfld]).Split('.')[1] + "' Field value added in '" + destfld.Split('.')[1] + "' ");
                                        oListItem.Update();
                                        ctx.ExecuteQuery();
                                    }
                                }
                                lvi1 = lstView.Items.Add("");
                                lvi1.SubItems.Add(dtResults.Rows.Count + "  Records migrated in '" + mapColumns.destListName + "' List from '" + mapColumns.srcTableName + "' Table ");
                                lstView.Items[lstView.Items.Count - 1].ForeColor = Color.Green;
                            }
                            else
                            {
                                lvi1 = lstView.Items.Add("");
                                lvi1.SubItems.Add("No Records Found in '" + mapColumns.srcTableName + "' Table ");
                                lstView.Items[lstView.Items.Count - 1].ForeColor = Color.Red;
                                //MessageBox.Show("No Records Found in " + mapColumns.srcTableName + " Table ");
                            }
                        }
                    }
                }
                MessageBox.Show("Migration Completed Successfully");
            }
            catch (Exception ex)
            {
              MessageBox.Show(ex.Message);
            }
        }

        private Web getWeb(string weburl)
        {
            Web oweb = null; 
            try
            {
                using (ClientContext ctx = new ClientContext(weburl))
                {
                    oweb = ctx.Web;
                    ctx.Load(oweb);
                    ctx.ExecuteQuery();
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return oweb;
        }

    }
}
